/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import bentity.*;
import blogic.blHelper;
import java.awt.Point;
import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author giulio
 */
public class daoAlmacen {


//    public  void buscarPallets(){
//        PreparedStatement ps = conn.prepareStatement("SELECT * FROM USUARIO WHERE IDUSUARIO = ?");
//        ps.setInt(1, 2);
//        ResultSet rs = ps.executeQuery();
//        System.out.println("IDUSUARIO\tNOMBRE\tPASSWORD\tESTADO\tCARGO");
//        while(rs.next())
//            System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t"+rs.getString(4)+"\t"+rs.getString(5));
//    }
    public int fRegistrarAlmacen(beAlmacen alm) throws SQLException{
        
        Connection conn = daoHelper.dameConexion();
        String sql = "INSERT INTO ALMACEN (nombre, direccion, telefono, largo, ancho, numero_filas, numero_columnas) VALUES (?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, alm.getNombre());
        ps.setString(2, alm.getDireccion());
        ps.setString(3, alm.getTelf());
        ps.setDouble(4, alm.getLargo());
        ps.setDouble(5, alm.getAncho());
        ps.setInt(6, alm.getFilas());
        ps.setInt(7, alm.getRacksXFila());
        ps.executeUpdate();
        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;
    }
    
//    public int reconstruir() throws SQLException{
//
//        Connection conn = daoHelper.dameConexion();
//        String sql = "DELETE FROM UBICACION; DELETE FROM RACK";
//        PreparedStatement ps = conn.prepareStatement(sql);
//        ps.executeUpdate();
//        ps.close();
//        conn.close();
//        return daoHelper.TRANSACCION_EXITOSA;
//    }

    public Point filsColsActuales() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT MAX(NUMERO_FILA), MAX(NUMERO_RACK) FROM ALMACEN";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        Point p = new Point();
        while (rs.next()){
            p.x=rs.getInt(1);
            p.y=rs.getInt(2);
        }
        rs.close();
        ps.close();
        conn.close();
        return p;
    }

    public int pisoMax() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT MAX(NUMERO_PISO) FROM UBICACION";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        int p = 0;
        while (rs.next()){
            p=rs.getInt(1);
        }
        rs.close();
        ps.close();
        conn.close();
        return p;
    }

    public int fModificarAlmacen(beAlmacen alm) throws SQLException{

        Connection conn = daoHelper.dameConexion();
        String sql = "UPDATE ALMACEN set direccion = ?, telefono= ?, numero_filas=?, numero_columnas=? where id_almacen=1";
        //CAMBIAR LUEGO A ID_ALMACEN=1
        //CAMBIAR LUEGO A ID_ALMACEN=1
        //CAMBIAR LUEGO A ID_ALMACEN=1
        //CAMBIAR LUEGO A ID_ALMACEN=1
        
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, alm.getDireccion());
        ps.setString(2, alm.getTelf());
        ps.setInt(3, alm.getFilas());
        ps.setInt(4, alm.getRacksXFila());
        ps.executeUpdate();
        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;

    }

    public void fModificartTipoPallet(int tipo) throws SQLException{

        Connection conn = daoHelper.dameConexion();
        String sql = "UPDATE UBICACION SET TIPO_PALLET= ?";

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, tipo);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
    
    public int fRegistrarTipoProducto(beTipoProducto tip) throws SQLException, Exception{
        
        Connection conn = daoHelper.dameConexion();
        String sql = "INSERT INTO TIPO_PRODUCTO(descripcion) VALUES (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, tip.getDescripcion());
        int id=new daoHelper().getNextId("TIPO_PRODUCTO","ID_TIPO_PRODUCTO");
        ps.executeUpdate();        
        ps.close();
        conn.close();
        return id;  
  
    }

    public void fUpdPisoUbsRack(int pis, int ubs, int ra) throws SQLException{

        Connection conn = daoHelper.dameConexion();
        String sql = "UPDATE RACK SET CANTIDAD_PISOS= ?, CANTIDAD_UBICACIONES= ? WHERE ID_RACK=?";

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, pis);
        ps.setInt(2, ubs);
        ps.setInt(3, ra);
        ps.executeUpdate();
        ps.close();
        conn.close();

    }

    public int fRegistrarEntrada(beEntrada ent) throws SQLException{

        Connection conn = daoHelper.dameConexion();
        String sql = "INSERT INTO ENTRADA(posicion_x, posicion_y, id_almacen, rotulo, estado_entrada) VALUES (?, ?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, ent.getPos_x());
        ps.setInt(2, ent.getPos_y());
        ps.setInt(3, 1);//debería ser siempre 1
        ps.setString(4, ent.getRotulo());
        ps.setDouble(5, 12);
        ps.executeUpdate();
        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;

    }
    
    public beAlmacen buscarAlmacen() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        String sql = "SELECT nombre, direccion, telefono, largo, ancho, numero_filas, numero_columnas FROM ALMACEN";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        
        rs = ps.executeQuery();
        
        beAlmacen c = null;
        while (rs.next()){
            c = new beAlmacen();
            c.setNombre(rs.getString(1));
            c.setDireccion(rs.getString(2));
            c.setTelf(rs.getString(3));
            c.setLargo(rs.getDouble(1));
            c.setAncho(rs.getDouble(5));
            c.setFilas(rs.getInt(6));
            c.setRacksXFila(rs.getInt(7));
        }
        rs.close();
        ps.close();
        conn.close();
        return c;
    }

    public String[] buscarEntradas() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT ROTULO FROM ENTRADA";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        String []ents=new String[80];
        int i=0;
        while (rs.next()){
            ents[i]=rs.getString(1);
            i++;
        }
        rs.close();
        ps.close();
        conn.close();
        return ents;
    }
    
    public Integer obtenerRack(int fil, int col) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT ID_RACK FROM RACK WHERE NUMERO_FILA=? AND NUMERO_RACK=?";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, fil);
        ps.setInt(2, col);
        
        rs = ps.executeQuery();
        
        int i=0;
        while (rs.next()){
            i=rs.getInt(1);
        }
        rs.close();
        ps.close();
        conn.close();
        return i;
    }

    public Integer obtenerUbicacion(int ra, int n_piso, int n_ubi) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT ID_UBICACION FROM UBICACION WHERE NUMERO_PISO=? AND NUMERO_UBICACION=? AND ID_RACK=?";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, n_piso);
        ps.setInt(2, n_ubi);
        ps.setInt(3, ra);

        rs = ps.executeQuery();

        int i=0;
        while (rs.next()){
            i=rs.getInt(1);
        }
        rs.close();
        ps.close();
        conn.close();
        return i;

    }

    public ArrayList<beEntrada> buscarObjsEntradas() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT * FROM ENTRADA WHERE ESTADO_ENTRADA=12";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        ArrayList<beEntrada> ents=new ArrayList<beEntrada>();
        while (rs.next()){
            beEntrada beEnt =new beEntrada();
            beEnt.setPos_x(rs.getInt("POSICION_X"));
            beEnt.setPos_y(rs.getInt("POSICION_Y"));
            beEnt.setRotulo(rs.getString("ROTULO"));
            ents.add(beEnt);
        }
        rs.close();
        ps.close();
        conn.close();
        return ents;
    }

    public ArrayList<String> buscarTiposProductos() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT DESCRIPCION FROM TIPO_PRODUCTO";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        ArrayList<String> ents=new ArrayList<String>();
        while (rs.next()){
            ents.add(rs.getString("DESCRIPCION"));
        }
        rs.close();
        ps.close();
        conn.close();
        return ents;
    }

    public beAlmacen obtenerAlmacen() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT * FROM ALMACEN";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();
        beAlmacen bealm =new beAlmacen();
        int cant=0;
        while (rs.next()){

            bealm.setNombre(rs.getString("NOMBRE"));
            bealm.setDireccion(rs.getString("DIRECCION"));
            bealm.setTelf(rs.getString("TELEFONO"));
            bealm.setLargo(rs.getDouble("LARGO"));
            bealm.setAncho(rs.getDouble("ANCHO"));
            bealm.setFilas(rs.getInt("NUMERO_FILAS"));
            bealm.setRacksXFila(rs.getInt("NUMERO_COLUMNAS"));
        }
        rs.close();
        ps.close();
        conn.close();
        return bealm;
    }

    public beAlmacen obtenerAlmacenParaDespacho() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT * FROM ALMACEN";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();
        beAlmacen bealm =new beAlmacen();
        int cant=0;
        while (rs.next()){
            bealm.setLargo(rs.getDouble("LARGO"));
            bealm.setAncho(rs.getDouble("ANCHO"));
            bealm.setFilas(rs.getInt("NUMERO_FILAS"));
            bealm.setRacksXFila(rs.getInt("NUMERO_COLUMNAS"));
            bealm.setNombre(rs.getString("NOMBRE"));
        }
        rs.close();
        ps.close();
        conn.close();
        return bealm;
    }

    public int hayAlmacen() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT COUNT(*) FROM ALMACEN";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

       int cant=0;
        while (rs.next()){
            cant=rs.getInt(1);
        }
        rs.close();
        ps.close();
        conn.close();
        return cant;
    }

    public void borrarEntradas() throws SQLException {
        Connection conn = daoHelper.dameConexion();
        String sql = "UPDATE ENTRADA set estado_entrada= ?";

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 13);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
    
    public void borrarSecciones() throws SQLException {
        Connection conn = daoHelper.dameConexion();
        String sql = "UPDATE RACK SET ID_TIPO_PRODUCTO=NULL WHERE ID_TIPO_PRODUCTO IS NOT NULL";

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
}
